package persistence;

import java.sql.*;
import java.io.*;

import common.*;
import java.awt.Cursor;

public class TransactionManager {
	
	/**
	 * pointer to the current open connection
	 */
	private static Connection _connection;

	/** 
	 * called to initialize the class with the JDBC driver
	 * @return returns true upon success, false otherwise
	 */
	public static boolean initialize() 
	{
		try {
			final String driver = "org.sqlite.JDBC";
			Class.forName(driver);
		}
		catch(ClassNotFoundException e) 
		{
			System.out.println("Error: couldn't find the org.sqlite.JDBC driver");
			return false;
		}
		return true;
	}

    
	
	private TransactionManager() {
		return;
	}
	
	/**
	 * returns a Connection object on which to perform a transaction
	 * @return
	 */
	public static Connection getConnection() {
		return _connection;
	}

	/**
	 * call to begin a new transaction, 
	 * need to call end on old transaction before calling begin
	 * @return retruns true upon success, false otherwise
	 */
	public static boolean begin() 
	{
		if(_connection != null)
			return false;
		String dbName = "db" + File.separator + "hit.sqlite";
		String connectionURL = "jdbc:sqlite:" + dbName;
		try {
			assert (_connection == null);
			
			_connection = DriverManager.getConnection(connectionURL);
			_connection.setAutoCommit(false);
		}
		catch (SQLException e) {
			System.out.println("Error: " + e.getMessage());
			e.printStackTrace();
		}
                
		int numOfTables = countTables();
                
                if(numOfTables == 0)
                {
                    createTables();
                }
                
                countTables();
                
		return true;
		
	}
	
	/**
	 * commits or rolls back the changes based on @param commit parameter
	 * @param commit true means the transaction will be committed, false will rollback any changes
	 * @return returns true opon success, false otherwise
	 */
	public static boolean end(boolean commit) 
	{
		assert (_connection != null);
		boolean result = true;
		
		try {
			if (commit) {
				_connection.commit();
			}
			else {
				_connection.rollback();
				System.out.println("Rolling back DB Changes");
			}
			result = true;
		}
		catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		finally {
			DbUtils.safeClose(_connection);
		}
		
		_connection = null;
		return result;
	}
        
        private static void createTables() {
            String createItemsTable = "CREATE TABLE Items "
                    + "(ID INTEGER PRIMARY KEY  NOT NULL UNIQUE, "
                    + "barcode varchar(12) NOT NULL, productID INTEGER NOT NULL, "
                    + "entryDate varchar(30) NOT NULL, exitTime varchar(30), " 
                    + "expDate varchar(30), containerID INTEGER,removed BOOLEAN);";
            
            String createProductsTable = "CREATE TABLE Products "
                    + "(ID  INTEGER PRIMARY KEY  NOT NULL UNIQUE, " +
            		"barcode varchar(12) NOT NULL, description varchar(255)"
                    + " NOT NULL, creationDate varchar(30) NOT NULL, " +
            		"amountNum FLOAT NOT NULL, amountType varchar(12) "
                    + "NOT NULL, shelfLife INTEGER NOT NULL, " +
            		"threeMonthSupply INTEGER NOT NULL constraint amt_ck check "
                    + "(amountType in " +
            		"('count','pounds','ounces','grams','kilograms',"
                    + "'gallons','quarts','pints','fluidOunces','liters'))," +
            		"  removed BOOL DEFAULT 0)";
            
            String createProductContanersTable = "CREATE TABLE ProductContainers "
                    + "(ID INTEGER PRIMARY KEY  NOT NULL  UNIQUE, name varchar(255)"
                    + " NOT NULL, containerType varchar(15) NOT NULL, parentID INTEGER,"
                    + " threeMonthNum FLOAT, threeMonthType varchar(12)constraint "
                    + "amt_ck check (threeMonthType in ('count','pounds','ounces',"
                    + "'grams','kilograms','gallons','quarts','pints','fluidOunces','liters'))"
                    + "constraint cont_type_ck check (containerType in ('storage unit',"
                    + "'product container')));";
            
            String createContainerToProductsTable = "CREATE TABLE ContainerToProducts "
                    + "(containerID INTEGER NOT NULL, productID INTEGER NOT NULL);";
            
            String HITConfigTable = "CREATE TABLE HITConfig"
                    + "(lastRemovedItemsReportDate varchar(30),lastPGID INTEGER);";
            
            Connection connection = getConnection();
            Statement keyStmt = null;
            ResultSet keyRS = null;
            
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(createItemsTable);
                connection.close();
            }
            catch(SQLException e)
            {}
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(createProductsTable);
                connection.close();
            }
            catch(SQLException e)
            {}
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(createItemsTable);
                connection.close();
            }
            catch(SQLException e)
            {}
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(createProductContanersTable);
                connection.close();
            }
            catch(SQLException e)
            {}
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(createContainerToProductsTable);
                connection.close();
            }
            catch(SQLException e)
            {}
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(HITConfigTable);
                connection.close();
            }
            catch(SQLException e)
            {}
            
        }
        
        private static int countTables() {
            int count = 0;
            String SQL_GET_ALL_TABLES = "SELECT * FROM sqlite_master WHERE type='table'";
            Connection connection = getConnection();
            Statement keyStmt = null;
            ResultSet keyRS = null;
            int numOfTables = 0;
            
            try{
                keyStmt = TransactionManager.getConnection().createStatement();
                keyRS = keyStmt.executeQuery(SQL_GET_ALL_TABLES);
                
                while(keyRS.next())
                {
                    numOfTables++;
                }
                
                //System.out.println("number of tables in db: " + numOfTables);

                
            }
            catch(SQLException e)
            {}
			 
            
            return numOfTables;
            
        }
	
	
}
